﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.IO;
using System.Data.OleDb;

namespace StockClientUI
{
    public partial class UserGroup : DevExpress.XtraEditors.XtraForm
    {
        public UserGroup()
        {
            InitializeComponent();
        }

        private void UserGroup_Load(object sender, EventArgs e)
        {
           gridControl1.DataSource= MdiMain.getds.Tables[0].DefaultView;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //DevExpress.XtraExport.ExportXlsProvider xp = new DevExpress.XtraExport.ExportXlsProvider();
           
            gridControl1.ExportToXls(Application.StartupPath+@"\test.xls");
            MessageBox.Show("导出成功");

        }



        //转换
        public System.Data.DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strExcelFileName + ";extended properties='Excel 8.0;HDR=NO;IMEX=1;'";           
            string strExcel = string.Format("select * from [{0}$]", strSheetName);
            DataSet ds = new DataSet();
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {   conn.Open();
                OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
                adapter.Fill(ds, strSheetName);
                conn.Close();
            }
            return ds.Tables[0];
        }

        private void button2_Click(object sender, EventArgs e)
        {
             this.openFileDialog1.Title = "请选择导入文件";
            this.openFileDialog1.FileName = "";
            this.openFileDialog1.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
            this.openFileDialog1.Filter = "excel文件(*.xls)|*.xls";
            this.openFileDialog1.ValidateNames = true;
            this.openFileDialog1.CheckFileExists = true;
            this.openFileDialog1.CheckPathExists = true;



            if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                DataTable table = ExcelToDataTable(this.openFileDialog1.FileName, "Sheet1");
                gridControl2.DataSource = table.DefaultView;
                gridView2.BestFitColumns();
                MessageBox.Show("成功导入" + table.Rows.Count.ToString() + "条数据信息");
            }

        }

        //public void CreateExcel(DataTable dt, string fileName)
        //{
        //    System.Diagnostics.Process[] arrProcesses;
        //    arrProcesses = System.Diagnostics.Process.GetProcessesByName("Excel");
        //    foreach (System.Diagnostics.Process myProcess in arrProcesses)
        //    {
        //        myProcess.Kill();
        //    }

        //    Object missing = Missing.Value;
        //   Excel.Application m_objExcel =new Excel.Application();
        //   Excel.Workbooks m_objWorkBooks = m_objExcel.Workbooks;          　
        //   Excel.Workbook m_objWorkBook = m_objWorkBooks.Add(true);
        //   Excel.Sheets m_objWorkSheets = m_objWorkBook.Sheets; ;
        //   Excel.Worksheet m_objWorkSheet =(Excel.Worksheet)m_objWorkSheets[1];
        //    int intFeildCount = dt.Columns.Count;
        //    for (int col = 0; col < intFeildCount; col++)
        //    {
        //        m_objWorkSheet.Cells[1, col + 1] = dt.Columns[col].ToString();
        //    }
        //    for (int intRowCount = 0; intRowCount < dt.Rows.Count; intRowCount++)
        //    {
        //        for (int intCol = 0; intCol < dt.Columns.Count; intCol++)
        //        {
        //            m_objWorkSheet.Cells[intRowCount + 2, intCol + 1] = "'" + dt.Rows[intRowCount][intCol].ToString();
        //        }
        //    }

        //    if (File.Exists(fileName))
        //    {
        //        File.Delete(fileName);
        //    }
        //    m_objWorkBook.SaveAs(fileName, missing, missing, missing, missing,
        //    missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
        //    missing, missing, missing, missing, missing);
        //    m_objExcel = null;

        //}

    }
}